﻿using MyWEbProject.DAO.Entities;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;

namespace MyWEbProject.DAO
{
    public class ProfessionDAO
    {
        SqlConnection _Conn = null;
        public ProfessionDAO()
        {
            String connectionString = "server=localhost\\SQLEXPRESS;database=NursatTestDB;uid=sa;password=Vizrt_123;";
            _Conn = new SqlConnection(connectionString);
            _Conn.Open();
        }
        public List<Profession> GetAll()
        {
            if (_Conn.State != System.Data.ConnectionState.Open)
                _Conn.Open();

            String SQL = "SELECT * FROM Profession";
            SqlCommand command = new SqlCommand(SQL, _Conn);
            command.CommandType = System.Data.CommandType.Text;
            SqlDataReader reader = null;
            try
            {
                reader = command.ExecuteReader();
                List<Profession> professions = new List<Profession>();
                while (reader.Read())
                {
                    Profession profession = new Profession();
                    profession.ID = Convert.ToInt32( reader["ID"]);
                    profession.Name = Convert.ToString(reader["Name"]);
                    professions.Add(profession);
                }
                reader.Close();
                return professions;
            }
            catch(Exception ex)
            {                
                throw ex;
            }
            finally
            {
                if (_Conn != null && _Conn.State == System.Data.ConnectionState.Open)
                    _Conn.Close();                
            }
        }

        public Profession GetByID(int id)
        {
            if (_Conn.State != System.Data.ConnectionState.Open)
                _Conn.Open();

            String SQL = String.Format("SELECT * FROM Profession WHERE ID = {0}", id) ;
            SqlCommand command = new SqlCommand(SQL, _Conn);
            command.CommandType = System.Data.CommandType.Text;
            SqlDataReader reader = null;
            try
            {
                reader = command.ExecuteReader();
                Profession profession = new Profession();
                while (reader.Read())
                {                    
                    profession.ID = Convert.ToInt32(reader["ID"]);
                    profession.Name = Convert.ToString(reader["Name"]);                    
                }
                reader.Close();
                return profession;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Conn != null && _Conn.State == System.Data.ConnectionState.Open)
                    _Conn.Close();
            }
        }
        
        public int Save(Profession profession)
        {
            if (_Conn.State != System.Data.ConnectionState.Open)
                _Conn.Open();

            String SQL = String.Empty;
            if (profession.ID == 0)
                SQL = String.Format("INSERT INTO Profession (Name) VALUES ('{0}')", profession.Name);
            else
                SQL = String.Empty;//TODO: UPDATE SQL Should be here
            SqlCommand command = new SqlCommand(SQL, _Conn);
            command.CommandType = System.Data.CommandType.Text;
            
            try
            {
                int effectedRows = command.ExecuteNonQuery();
                return effectedRows;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Conn != null && _Conn.State == System.Data.ConnectionState.Open)
                    _Conn.Close();
            }
        }

        public int GetCount()
        {
            if (_Conn.State != System.Data.ConnectionState.Open)
                _Conn.Open();

            String SQL = String.Empty;
            
            SQL = String.Format("Select count(*) from Profession");
           
            SqlCommand command = new SqlCommand(SQL, _Conn);
            command.CommandType = System.Data.CommandType.Text;

            try
            {
                int Rows = command.ExecuteNonQuery();
                return Rows;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Conn != null && _Conn.State == System.Data.ConnectionState.Open)
                    _Conn.Close();
            }
        }

    }
}